﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using AMS.DataAccess.Trademark.BO;
using AMS.DataAccess.Sql;
using AMS.DataAccess.Common;
using AMS.DataAccess.Trademark.Interfaces;
using AMS.DataAccess.Trademark.BO.Enums;
using AMS.DataAccess.Trademark.Filters;

namespace AMS.DataAccess.Trademark
{
    public class CaseBasicInfoManager : ICaseBasicInfoManager
    {
        public const string CaseBasicInfoTableName = "TT_CaseBasicInfo";
        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="caseInfo"></param>
        public void AddCaseBasicInfo(CallContext context, CaseBasicInfo caseInfo)
        {
            caseInfo.CreateDateTime = DateTime.Now;
            caseInfo.VersionDateTime = DateTime.Now;
            // TODO: apply owner id here
            //caseInfo.CreateUserID = context.UserID;
            //caseInfo.VersionUser = context.UserID;

            SqlInsert sql = new SqlInsert(CaseBasicInfoTableName);
            sql.AddBusinessObject(caseInfo);
            sql.Add("SELECT @@IDENTITY");


            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            var ds = SqlDBHelper.Query(sql.ToString());
            caseInfo.AID = AdoConverter.ToInt32(ds.Tables[0].Rows[0][0]);
        }

        public bool UpdateCaseBasicInfo(CallContext context, CaseBasicInfo caseInfo)
        {
            SqlUpdate update = new SqlUpdate(CaseBasicInfoTableName);
            update.AddBusinessObject(caseInfo);
            // TODO: add version control and user id here
            update.AddCondition(new SqlCondition("AID", caseInfo.AID));

            AMS.DataAccess.SqlDebugger.WriteLine(update);

            return 0 < SqlDBHelper.ExecuteSql(update.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <param name="filter">CANNOT be null.</param>
        /// <returns></returns>
        public List<CaseDisplayInfo> GetCaseInfoList(CallContext context, CaseInfoFilter filter)
        {
            SqlBuilder sql = new SqlBuilder();
            string topExpr = string.Empty;
            if (filter != null && filter.LimitCount > 0)
            {
                topExpr = " TOP " + filter.LimitCount.ToString();
            }

            sql.Add("select", topExpr);
            sql.Add("     cb.AID");
            sql.Add("    ,cb.CaseIndex");
            sql.Add("    ,cb.CaseName");
            sql.Add("    ,cb.OwnerID");
            sql.Add("    ,cb.CustomerID");
            sql.Add("    ,cb.CaseType");
            sql.Add("    ,cb.CaseStatus");
            sql.Add("    ,cb.Attorney");
            if (filter != null)
            {
                if (filter.GetTrademarkInfo)
                {
                    sql.Add("    ,tm.Name as TMName");
                    sql.Add("    ,tm.RegisterNumber as TMRgisterNumber");
                }
                if (filter.GetCustomerApplicantInfo)
                {
                    sql.Add("    ,cus.Name as CustomerName");
                    sql.Add("    ,app.Name as ApplicantName");
                    sql.Add("    ,con.Name as ContactorName");
                }
            }

            sql.Add("from TT_CaseBasicInfo cb");
            if (filter != null)
            {
                if (filter.GetTrademarkInfo)
                {
                    sql.Add("LEFT JOIN TT_TradeMark tm on cb.TrademarkID = tm.AID");
                }
                if (filter.GetCustomerApplicantInfo)
                {
                    sql.Add("INNER JOIN TC_Customer cus on cb.CustomerID = cus.AID");
                    sql.Add("LEFT JOIN TT_Applicant appref on cb.AID = appref.CaseID AND appref.IsFirst = 1");
                    sql.Add("LEFT JOIN TC_Customer app on appref.ApplicantID = app.AID");
                    sql.Add("LEFT JOIN TT_Contactor conref on cb.AID = conref.CaseID AND conref.IsFirst = 1");
                    sql.Add("LEFT JOIN TC_Contactor con on conref.ContactorID = con.AID");
                }
            }

            sql.Add("WHERE 1 = 1");
            if (filter != null)
            {
                if (!filter.GetFinishedCase)
                {
                    sql.Add("    AND ", new SqlCondition("cb.CaseStatus", (int)TMCaseStatus.Finished, SqlOperations.NotEquals));
                }
                if (filter.CaseType != null)
                {
                    sql.Add("    AND cb.CaseType", filter.CaseType.ToString());
                }
                if (filter.CaseIndex != null)
                {
                    sql.Add("    AND cb.CaseIndex", filter.CaseIndex.ToString());
                }
                if (filter.CaseName != null)
                {
                    sql.Add("    AND cb.CaseName", filter.CaseName.ToString());
                }
            }

            sql.Add("ORDER BY");
            sql.Add("    cb.AID DESC");

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            DataSet ds = SqlDBHelper.Query(sql.ToString());
            var list = BusinessObjectMapping.DataTableToBOList<CaseDisplayInfo>(ds.Tables[0]);
            return list;
        }

        public CaseDisplayInfo GetCaseBasicInfo(CallContext context, int caseId)
        {
            SqlBuilder sql = new SqlBuilder();
            sql.Add("SELECT cb.*");
            sql.Add("    ,cus.Name as CustomerName");
            sql.Add("FROM TT_CaseBasicInfo cb");
            sql.Add("INNER JOIN TC_Customer cus ON cb.CustomerID = cus.AID");
            sql.Add("WHERE ", new SqlCondition("cb.AID", caseId));

            AMS.DataAccess.SqlDebugger.WriteLine(sql);

            var ds = SqlDBHelper.Query(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return BusinessObjectMapping.DataRowToBO<CaseDisplayInfo>(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
    }
}
